﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SD.IDAL;
using SD.Entities;
using System.Data.SqlClient;
using System.Data;

namespace SD.DAL
{
    public class DepartmentDAO : BaseDAO, IDepartmentDAO
    {

        #region IDepartmentDAO Members

        /// <summary>
        /// Get list by filter
        /// </summary>
        /// <param name="filterExpression">filterExpression parammeter</param>
        /// <param name="includeInactive">includeOnactive parammeter</param>
        /// <returns>List of Department entity by filter</returns>
        public List<Department> GetByFilter(string filterExpression, bool includeInactive, string DirectorateId)
        {
            // Create a command object
            SqlCommand comm = CreateCommand();
            // Call command by store
            comm.CommandText = "DepartmentGetByFilter";
            // Add parammeter to store procedure
            comm.Parameters.AddWithValue("@filterExpression", filterExpression);
            comm.Parameters.AddWithValue("@includeInactive", includeInactive);
            comm.Parameters.AddWithValue("@DirectorateID", DirectorateId);

            // Create a data table
            DataTable dt = ExecuteSelectCommand(comm);
            // Get list of directorate
            List<Department> list = new List<Department>();
            Department depart = null;

            // Browse the list and add to table
            foreach (DataRow row in dt.Rows)
            {
                depart = new Department();
                depart.DepartmentId = row["DepartmentID"].ToString();
                depart.Name = row["Name"].ToString();
                depart.Address = row["AddressID"] == DBNull.Value ? null : new AddressDAO().GetById(row["AddressID"].ToString());
                depart.Contact = row["ContactID"] == DBNull.Value ? null : new ContactDAO().GetById(row["ContactID"].ToString());
                depart.IsActive = bool.Parse(row["IsActive"].ToString());
                list.Add(depart);
            }

            // Return the list
            return list;
        }

        /// <summary>
        /// Get list by status
        /// </summary>
        /// <param name="includeInactive">includeInactive parammeter</param>
        /// <returns>List of Department entity by status</returns>
        public List<Department> GetAll(bool includeInactive, string DirectorateId)
        {
            // Create a command object
            SqlCommand comm = CreateCommand();
            // Call command by store
            comm.CommandText = "DepartmentGetAll";
            // Add parammeter to store procedure        
            comm.Parameters.AddWithValue("@includeInactive", includeInactive);
            comm.Parameters.AddWithValue("@DirectorateID", DirectorateId);
            // Create a data table
            DataTable dt = ExecuteSelectCommand(comm);
            // Get list of directorate
            List<Department> list = new List<Department>();
            Department depart = null;

            // Browse the list and add to table
            foreach (DataRow row in dt.Rows)
            {
                depart = new Department();
                depart.DepartmentId = row["DepartmentID"].ToString();
                depart.Name = row["Name"].ToString();
                depart.Address = row["AddressID"] == DBNull.Value ? null : new AddressDAO().GetById(row["AddressID"].ToString());
                depart.Contact = row["ContactID"] == DBNull.Value ? null : new ContactDAO().GetById(row["ContactID"].ToString());
                depart.IsActive = bool.Parse(row["IsActive"].ToString());
                list.Add(depart);
            }

            // Return the list
            return list;
        }

        /// <summary>
        /// Get Department entity by departmentID
        /// </summary>
        /// <param name="departmentId">departmentID parammeter</param>
        /// <returns>Department entity by departmentID</returns>
        public Department GetById(string departmentId)
        {
            // Obtain SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure
            comm.CommandText = "DepartmentGetById";

            // Add parameter to stored procedure
            comm.Parameters.AddWithValue("@DepartmentID", departmentId);

            // Execute stored procedure
            DataTable dt = ExecuteSelectCommand(comm);

            Department dePart = null;

            // if table has row
            if (dt.Rows.Count > 0)
            {

                dePart = new Department();
                dePart.DepartmentId = dt.Rows[0]["DepartmentID"].ToString();
                dePart.Name = dt.Rows[0]["Name"].ToString();
                dePart.ShortDescription = dt.Rows[0]["ShortDescription"].ToString();
                dePart.Directorate = dt.Rows[0]["DirectorateID"] == DBNull.Value ? null : new DirectorateDAO().GetById(dt.Rows[0]["DirectorateID"].ToString());
                dePart.Address = dt.Rows[0]["AddressID"] == DBNull.Value ? null : new AddressDAO().GetById(dt.Rows[0]["AddressID"].ToString());
                dePart.Contact = dt.Rows[0]["ContactID"] == DBNull.Value ? null : new ContactDAO().GetById(dt.Rows[0]["ContactID"].ToString());
                dePart.FullDescription = dt.Rows[0]["FullDescription"].ToString();
                dePart.Phone = dt.Rows[0]["Phone"].ToString();
                dePart.Fax = dt.Rows[0]["Fax"].ToString();
                dePart.Web = dt.Rows[0]["Web"].ToString();
                dePart.IsActive = bool.Parse(dt.Rows[0]["IsActive"].ToString());
                dePart.BusinessType = dt.Rows[0]["BusinessType"] == DBNull.Value ? null : new ReferenceDataDAO().GetById(dt.Rows[0]["BusinessType"].ToString());
                dePart.Email = dt.Rows[0]["Email"].ToString();


            }

            // return true if success fully, otherwise false
            return dePart;        
        }

        /// <summary>
        /// Insert a department by department entity
        /// </summary>
        /// <param name="department">department Parammeter</param>
        /// <returns>Return result of insert. If success return true, else return false</returns>
        public string Insert(Department department)
        {
            string result;

            // Create new command object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure
            comm.CommandText = "DepartmentInsert";

            // Add parameters to commnand
            comm.Parameters.AddWithValue("@Name", department.Name);
            comm.Parameters.AddWithValue("@ShortDescription", department.ShortDescription);
            comm.Parameters.AddWithValue("@ContactID", department.Contact == null ? null : department.Contact.ContactId);
            comm.Parameters.AddWithValue("@AddressID", department.Address.AddressId);
            comm.Parameters.AddWithValue("@FullDescription", department.FullDescription);
            comm.Parameters.AddWithValue("@Phone", department.Phone);
            comm.Parameters.AddWithValue("@Fax", department.Fax);
            comm.Parameters.AddWithValue("@Web", department.Web);
            comm.Parameters.AddWithValue("@IsActive", department.IsActive);
            comm.Parameters.AddWithValue("@BusinessType", department.BusinessType.RefId);
            comm.Parameters.AddWithValue("@DirectorateID", department.Directorate.DirectorateId);
            comm.Parameters.AddWithValue("@Email", department.Email);

            try
            {
                comm.Connection.Open();
                // Execute the stored procedure
                result = comm.ExecuteScalar().ToString();
                comm.Connection.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            // Result will be 1 in case of success
            return result;
        }

        public List<Department> GetDirByName(string dirName)
        {
            // Create new SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure
            comm.CommandText = "DepartmentGetByName";

            // Add parameters to stored procedure
            comm.Parameters.AddWithValue("@DirName", dirName);

            // Execute stored procedure
            DataTable dt = ExecuteSelectCommand(comm);

            List<Department> list = new List<Department>();

            // add each org to list
            foreach (DataRow row in dt.Rows)
            {
                Department dePart = new Department();
                dePart.DepartmentId = row["DepartmentID"].ToString();
                dePart.Name = row["Name"].ToString();
                dePart.Address = row["AddressID"] == DBNull.Value ? null : new AddressDAO().GetById(row["AddressID"].ToString());
                dePart.Contact = row["ContactID"] == DBNull.Value ? null : new ContactDAO().GetById(row["ContactID"].ToString());
                dePart.IsActive = bool.Parse(row["IsActive"].ToString());
                list.Add(dePart);
            }

            // return list organization
            return list;
        }
        /// <summary>
        /// Update a department entity by departmentID
        /// </summary>
        /// <param name="department">department parammeter</param>
        /// <returns>Return result of update. If update success return true, else return false</returns>
        public bool Update(Department department)
        {
            int result;

            // Create new SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure to command
            comm.CommandText = "DepartmentUpdate";

            // Add parameters to command
            comm.Parameters.AddWithValue("@DepartmentID", department.DepartmentId);
            comm.Parameters.AddWithValue("@Name", department.Name);
            comm.Parameters.AddWithValue("@ShortDescription", department.ShortDescription);
            comm.Parameters.AddWithValue("@ContactID", department.Contact == null ? null : department.Contact.ContactId);
            comm.Parameters.AddWithValue("@AddressID", department.Address == null ? null : department.Address.AddressId);
            comm.Parameters.AddWithValue("@FullDescription", department.FullDescription);
            comm.Parameters.AddWithValue("@Phone", department.Phone);
            comm.Parameters.AddWithValue("@Fax", department.Fax);
            comm.Parameters.AddWithValue("@Web", department.Web);
            comm.Parameters.AddWithValue("@IsActive", department.IsActive);
            comm.Parameters.AddWithValue("@BusinessType", department.BusinessType == null ? null : department.BusinessType.RefId);
            comm.Parameters.AddWithValue("@Email", department.Email);

            try
            {
                // Execute the stored procedure
                result = ExecuteNonQuery(comm);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            // Result will be 1 in case of success
            return (result != -1);
        }
        /// <summary>
        /// Set Active a Department
        /// </summary>
        /// <param name="dirId"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public bool SetActive(string departmentId, bool status)
        {
            int result;

            // Create new SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure to command
            comm.CommandType = CommandType.Text;
            comm.CommandText = "UPDATE Department SET IsActive = @IsActive WHERE DepartmentID = @DepartID";
            comm.Parameters.AddWithValue("@DepartID", departmentId);
            comm.Parameters.AddWithValue("@IsActive", status);

            try
            {
                // Execute the stored procedure
                result = ExecuteNonQuery(comm);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            // Result will be 1 in case of success
            return (result != -1);
        }
        /// <summary>
        /// Delete a department bu departmentID
        /// </summary>
        /// <param name="orgId">orgId parammeter</param>
        /// <returns>Return result of delete. If delete success return true, else return false</returns>
        public bool Delete(string orgId)
        {
            throw new NotImplementedException();
        }

        #endregion
    }
}
